﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using TruckScale.Helpers;
using static TruckScale.Helpers.LogHelper;

namespace TruckScale.Models
{
  public class WeighRecordModel
  {
    public string Bh { get; set; }//编号
    public string Ch { get; set; }//车号
    public int? Wz { get; set; }//物资
    public decimal Dj { get; set; }//单价
    public int? Kh { get; set; }//客户
    public int? Kh2 { get; set; }//客户
    public int? Kh3 { get; set; }//客户
    public int? Kh4 { get; set; }//客户
    public string Mz { get; set; }//毛重
    public string Mzsj { get; set; }//毛重时间
    public int? Mzsby { get; set; }//毛重司磅员
    public string Pz { get; set; }//皮重
    public string Pzsj { get; set; }//皮重时间
    public int? Pzsby { get; set; }//皮重司磅员
    public string Jz { get; set; }//净重
    public string Jzsj { get; set; }//净重时间，用于称重记录时间段统计
    //public string Kz { get; set; }//扣重
    //public string Sz { get; set; }//实重
    public string Bz { get; set; }//备注
    public string By1 { get; set; }//备用字段
    public string By2 { get; set; }//备用字段
    public string By3 { get; set; }//备用字段
    public string By4 { get; set; }//备用字段
    public string By5 { get; set; }//备用字段
    public string By6 { get; set; }//备用字段
    public string By7 { get; set; }//备用字段
    public string By8 { get; set; }//备用字段
    public string By9 { get; set; }//备用字段
    public string By10 { get; set; }//备用字段
    public string Weighbridge1 { get; set; }//第一次称重地磅名称
    public string Weighbridge2 { get; set; }//第二次称重地磅名称
    public string WeighTemplate { get; set; }//称重模版
    public int WeighCount { get; set; }//称重次数，统计时用作总车次
    public bool IsFinish { get; set; }//是否完成称重
    public bool IsValid { get; set; }//有效位
    public string Dysj { get; set; }//打印时间，数据库中没有，打印时修改
    public decimal TotalMz { get; set; }//总毛重，数据库中没有，统计用
    public decimal TotalJz { get; set; }//总净重，数据库中没有，统计用
    //public decimal TotalSz { get; set; }//总实重，数据库中没有，统计用

    // 用于绑定显示外键对应的名称
    public string WzName { get; set; }
    public string KhName { get; set; }
    public string Kh2Name { get; set; }
    public string Kh3Name { get; set; }
    public string Kh4Name { get; set; }
    public string MzsbyName { get; set; }
    public string PzsbyName { get; set; }

    // 属性名与数据库字段名的映射
    public static Dictionary<string, string> propertyToDbFieldMap = new Dictionary<string, string>
    {
        { "WeighTemplate", "weigh_template" },
        { "WeighCount", "weigh_count" },
        { "IsFinish", "is_finish" },
        { "IsValid", "is_valid" },
    };

    public static List<WeighRecordModel> SelectAllRecord()
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "select * from weighrecord where is_valid = 1 order by auto_no desc;";
          IEnumerable<WeighRecordModel> output = cnn.Query<WeighRecordModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    public static List<WeighRecordModel> SelectUnFinishRecord(string start, string end)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from weighrecord where is_valid = 1 and mzsj >= '{start}' and mzsj <= '{end}' and is_finish = 0 order by 'auto_no' desc;";
          IEnumerable<WeighRecordModel> output = cnn.Query<WeighRecordModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    public static List<WeighRecordModel> SelectFinishRecord(string start, string end)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from weighrecord where is_valid = 1 and jzsj >= '{start}' and jzsj <= '{end}' and is_finish = 1 order by 'auto_no' desc;";
          IEnumerable<WeighRecordModel> output = cnn.Query<WeighRecordModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    public static WeighRecordModel SelectRecordByBh(string bh)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from weighrecord where is_valid = 1 and bh = '{bh}';";
          return cnn.QueryFirstOrDefault<WeighRecordModel>(sql);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    public static WeighRecordModel SelectCount(string start, string end)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select SUM(mz) AS TotalMz, SUM(jz) AS TotalJz, COUNT(*) AS WeighCount FROM weighrecord WHERE is_valid = 1 and jzsj >= '{start}' and jzsj <= '{end}' and is_finish = 1 ;";
          return cnn.QueryFirstOrDefault<WeighRecordModel>(sql);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    public static int SelectBh(string date)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select RIGHT(bh,4) from weighrecord where bh like '%{date}%' order by Bh desc limit 1;";
          int output = cnn.Query<int>(sql).FirstOrDefault();
          return output;
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }

    public static int InsertRecord(WeighRecordModel record)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"insert into weighrecord (bh, ch, wz, dj, kh, kh2, kh3, kh4, mz, mzsj, mzsby, pz, pzsj, pzsby, jz, jzsj, bz, by1, by2, by3, by4, by5, by6, by7, by8, by9, by10, weighbridge1, weighbridge2, weigh_template, weigh_count, is_finish) " +
            $"values (@Bh, @Ch, @Wz, @Dj, @Kh, @Kh2, @Kh3, @Kh4, @Mz, @Mzsj, @Mzsby, @Pz, @Pzsj, @Pzsby, @Jz, @Jzsj, @Bz, @By1, @By2, @By3, @By4, @By5, @By6, @By7, @By8, @By9, @By10, @Weighbridge1, @Weighbridge2, @WeighTemplate, @WeighCount, @IsFinish);";
          return cnn.Execute(sql, record);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }

    public static int UpdateRecord(WeighRecordModel record)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "update weighrecord set wz=@Wz, dj=@Dj, kh=@Kh, kh2=@Kh2, kh3=@Kh3, kh4=@Kh4, mz=@Mz, mzsj=@Mzsj, mzsby=@Mzsby, pz=@Pz, pzsj=@Pzsj, pzsby=@Pzsby, jz=@Jz, jzsj=@Jzsj, bz=@Bz, by1=@By1, by2=@By2, by3=@By3, by4=@By4, by5=@By5, by6=@By6, by7=@By7, by8=@By8, by9=@By9, by10=@By10, weighbridge2=@WeighBridge2, weigh_template=@WeighTemplate, weigh_count=@WeighCount, is_finish=@IsFinish where bh=@Bh";

          return cnn.Execute(sql, record);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }

    public static int DeleteRecord(WeighRecordModel record)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "update weighrecord set is_valid=@IsValid where bh=@Bh";

          SetLog($"删除称重记录{record.Bh}", LogType.OperationLogger, LogLevel.Info);

          return cnn.Execute(sql, record);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static void BatchDeleteRecords(List<WeighRecordModel> records)
    {
      if (records == null || records.Count == 0) return;

      var bhs = records.Select(r => r.Bh).ToList();

      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        cnn.Open();
        using (var transaction = cnn.BeginTransaction())
        {
          try
          {
            // 单次批量更新
            cnn.Execute(
                "UPDATE weighrecord SET is_valid = 0 WHERE Bh IN @Bhs",
                new { Bhs = bhs },
                transaction: transaction
            );

            transaction.Commit();
          }
          catch (Exception ex)
          {
            transaction.Rollback();
            throw new Exception("批量删除失败，已回滚", ex);
          }
        }
      }
    }

    //根据车牌号和过期时间查询是否有未完成的称重记录
    public static WeighRecordModel LoadUnexpiredWeighRecord(string PlateNo, string WeighExpiryDate)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from weighrecord where is_valid = 1 and is_finish = 0 and ch = '{PlateNo}'";

          if (WeighExpiryDate != "0")
          {
            WeighExpiryDate = DateTime.Now.AddHours(0 - Convert.ToDouble(WeighExpiryDate)).ToString("yyyy-MM-dd HH:mm:ss");
            sql += $" and(mzsj > '{WeighExpiryDate}' or pzsj > '{WeighExpiryDate}');";
          }

          return cnn.QueryFirstOrDefault<WeighRecordModel>(sql, new DynamicParameters());
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    //查询称重记录
    public static List<WeighRecordModel> SelectFliterRecord(string sql, DynamicParameters parameters)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          return cnn.Query<WeighRecordModel>(sql, parameters).ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
  }
}
